#!/usr/bin/python

import MySQLdb
import os
import string
import sys
import re
import time
import zipfile
from datetime import datetime



# check if the number of arguments is correct
if(len(sys.argv) != 6):
    print "Error:\tLess than 6 arguments provided"
    print "Usage:\tdatabase_loader.py host user password database directory_of_comparisons"
    print "Example:\tdatabase_loader.py localhost slamusers slamusers slamusers /srv/danio/slam_output"
    sys.exit(0)

# params to the script for loading
host = sys.argv[1]
user = sys.argv[2]
password = sys.argv[3]
database = sys.argv[4]
workingdir = sys.argv[5]

# dump the info just in case
print ""
print "Database Host: %s" % host
print "Database: %s" % database
print "Processing Directory: %s" % workingdir
print ""
print "Waiting 10 seconds before continuing..."
time.sleep(10);

# setup the database connection we're using
conn = MySQLdb.connect (host, user, password, database)
cursor = conn.cursor ()

#extension
extension = ".bed.xls"

#get the directory listing of the dir we were passed in
dirlisting = os.listdir(workingdir)


for subdir in dirlisting:
    dt = datetime.now()
    filename = workingdir + "/" + subdir + "/" + subdir+extension
    #print filename
    
    print "Processing File: %s" % filename
    
    try:
        # read the file and iterate over the lines
        file = open(filename)
    
    except Exception, e:
        print "Error opening file: " + filename
        print "Are you sure this file exists?"
        print "Skipping this filename."
        continue
    
    # split the filename
    only_filename =  os.path.split(filename)[1]
    only_filename = only_filename.split("_")
    line1 = only_filename[0]
    line2 = only_filename[1].split(".bed")[0]
    
    # counter to skip the header
    skipHeader = 0
    insertCount = 0
    for line in file.readlines():
        
        if skipHeader < 3:
            skipHeader += 1
    #        print line
            continue
        
        # strip the lines of trailing whitespace, then split it on tabs
        line = line.strip()
        split = line.split("\t")
    
    
        chromosome = split[0]
        start_position = split[1]
        end_position = split[2]
        region_name = split[3]
        score = split[4]
        n_probes = split[5]
        max_min_z_value = split[6]
        max_prop_diff = split[7]
        avg_treat_p = split[8]
        avg_con_p = split[9]
        
        
        sql = "insert into bed_data (line1, line2, chromosome, start_position, end_position, region_name, score, n_probes, max_min_z_value, max_prop_diff, average_treat_p, avg_con_p) values('" + line1 + "', '" + line2 + "', '" + chromosome + "', '" + start_position + "', '" + end_position + "', '" + region_name + "', '" + score + "', '" + n_probes + "', '" + max_min_z_value + "', '" + max_prop_diff + "', '" + avg_treat_p + "', '" + avg_con_p + "')"
    #    if(insertCount == 0 or insertCount == 5):
    #        print sql
    #        print n_probes           
    
        
        try:
            cursor.execute(sql)
            insertCount += 1
                
        except Exception, e:
            print "Error on line: " + line
            print "Insert Skipped"
            print sql
            print e
            
    #commit after every file
    conn.commit()
    print "Inserted " + str(insertCount) + " rows from this SLAM .bed file in " + str((datetime.now()-dt))